var dbase

function init()
{
	trace("Initing dbExtension")
	
	// get a reference to the database manager object
	// This will let you interact the database configure for this zone
	dbase = _server.getDatabaseManager()
}


function destroy()
{
	// Release the reference to the dbase manager
	delete dbase
}


function handleRequest(cmd, params, user, fromRoom)
{
	trace("Parametro cmd = " + cmd)
	var response = {}
	response._cmd = cmd;
	if (cmd == "getUsers")
	{
		// create a SQL statement
		var sql = "SELECT * FROM USERS"
		
		// execute query on DB
		// queryRes is a ResultSet object
		var queryRes = dbase.executeQuery(sql)

		// prepare the response object
		
		response._cmd = "getUsers"
		
		// Here we create an array for storing the database data
		response.db = []
		
		if (queryRes != null)
		{
			// Cycle through all records in the ResultSet
			for (var i = 0; i < queryRes.size(); i++)
			{
				// Get a record
				var tempRow = queryRes.get(i)
				
				// This object will hold the record data that we'll send to the client
				var item = {}
				
				// From the record object we can get each field value
				item.id = tempRow.getItem("ID")
				item.username = tempRow.getItem("USERNAME")
				item.money = tempRow.getItem("MONEY")
				item.points = tempRow.getItem("POINTS")
				item.position = tempRow.getItem("POSITION")
				item.victories = tempRow.getItem("VICTORIES")
				item.loses = tempRow.getItem("LOSES")
				item.racesPerDay = tempRow.getItem("RACES_PER_DAY")
				
				response.db.push( item )
			}
		}
		else
			trace("DB Query failed")
	}
	else if (cmd == "getTires")
	{
		// create a SQL statement
		var sql = "SELECT * FROM TIRES"
		
		// execute query on DB
		// queryRes is a ResultSet object
		var queryRes = dbase.executeQuery(sql)

		// prepare the response object
		
		response._cmd = "getTires"
		
		// Here we create an array for storing the database data
		response.tires = {};
		
		if (queryRes != null)
		{
			// Cycle through all records in the ResultSet
			for (var i = 0; i < queryRes.size(); i++)
			{
				// Get a record
				var tempRow = queryRes.get(i)
					
				// From the record object we can get each field value
				response.tires[i] = {};
				response.tires[i].id = tempRow.getItem("TIR_ID")
				response.tires[i].name = tempRow.getItem("TIR_NAME")
				response.tires[i].grip = tempRow.getItem("TIR_GRIP")
				response.tires[i].maxSpeed = tempRow.getItem("TIR_MAX_SPEED")
				response.tires[i].price = tempRow.getItem("TIR_PRICE")
			}
		}
		else
			trace("DB Query failed")
                
        }
	else if (cmd == "getEngines")
	{
		// create a SQL statement
		var sql = "SELECT * FROM ENGINES"
		
		// execute query on DB
		// queryRes is a ResultSet object
		var queryRes = dbase.executeQuery(sql)

		// prepare the response object
		
		response._cmd = "getEngines"
		
		// Here we create an array for storing the database data
		response.engines = {};
		
		if (queryRes != null)
		{
			// Cycle through all records in the ResultSet
			for (var i = 0; i < queryRes.size(); i++)
			{
				// Get a record
				var tempRow = queryRes.get(i)
				
				// From the record object we can get each field value
				response.engines[i] = {};
				response.engines[i].id = tempRow.getItem("ENG_ID")
				response.engines[i].name = tempRow.getItem("ENG_NAME")
				response.engines[i].acel = tempRow.getItem("ENG_ACELERATION")
				response.engines[i].maxSpeed = tempRow.getItem("ENG_MAX_SPEED")
				response.engines[i].price = tempRow.getItem("ENG_PRICE")
			}
		}
		else
			trace("DB Query failed")
                              
    }
	else if (cmd == "updatePlayer")
	{
		// Create the SQL statement to update the record
		var sql = "UPDATE USER SET "
		sql += " USR_MONEY='" + params.money + "',"
		sql += " USR_VICTORIES='" + params.victories + "',"
		sql += " USR_LOSES='" + params.loses + "',"
		sql += " USR_RACES_PER_DAY='" + params.racesPerDay + "',"
		sql += " WHERE USR_ID='" + params.id + "'"
			
		// execute query on DB
		// queryRes is a ResultSet object
		var successUser = dbase.executeCommand(sql)
		
		sql = "UPDATE CAR SET "
		sql += " CAR_ENGINE='" + params.car.engine.id + "',"
		sql += " CAR_TIRES='" + params.car.tires.id + "',"
		sql += " WHERE CAR_ID='" + params.car + "'"
			
		// execute query on DB
		// queryRes is a ResultSet object
		var successCar = dbase.executeCommand(sql)
		
		if (successUser && succesCar)
			response._cmd = "updatePlayer"
		else
			trace("DB Query failed")
                              
    }
	else if (cmd == "getPlayer")
	{               
		trace(params.username)
		
		response._cmd = "getPlayer"
		response.player = {}
		
		response.player = getPlayer(params.username, params.userId)
		
		if (response.player == null)
			trace("ERROR!");
    }
	else if (cmd == "buyTire")
	{
		trace("User Id: " + params.userId);
		trace("Money left: " + params.moneyLeft);
		trace("Tires Id: " + params.tiresId);
		// Create the SQL statement to update the record
		var sql = "UPDATE USER SET "
		sql += " USR_MONEY='" + params.moneyLeft + "'"
		sql += " WHERE USR_ID='" + params.userId + "'"
			
		// execute query on DB
		// queryRes is a ResultSet object
		var successUser = dbase.executeCommand(sql)
		
		sql = "UPDATE CARS SET "
		sql += " CAR_TIRES=" + params.tiresId
		sql += " WHERE CAR_ID=(SELECT USR_CAR FROM USER "
		sql += "WHERE USR_ID=" + params.userId + ")"
			
		// execute query on DB
		// queryRes is a ResultSet object
		var successCar = dbase.executeCommand(sql)
		
		if (successUser && successCar)
			response._cmd = "buyTire"
		else
			trace("DB Query failed")
                              
    }
	else if (cmd == "buyEngine")
	{
		trace("User Id: " + params.userId);
		trace("Money left: " + params.moneyLeft);
		trace("Engine Id: " + params.engineId);
		// Create the SQL statement to update the record
		var sql = "UPDATE USER SET "
		sql += " USR_MONEY='" + params.moneyLeft + "'"
		sql += " WHERE USR_ID='" + params.userId + "'"
			
		// execute query on DB
		// queryRes is a ResultSet object
		var successUser = dbase.executeCommand(sql)
		
		sql = "UPDATE CARS SET "
		sql += " CAR_ENGINE=" + params.engineId
		sql += " WHERE CAR_ID=(SELECT USR_CAR FROM USER "
		sql += "WHERE USR_ID=" + params.userId + ")"
			
		// execute query on DB
		// queryRes is a ResultSet object
		var successCar = dbase.executeCommand(sql)
		
		if (successUser && successCar)
			response._cmd = "buyTire"
		else
			trace("DB Query failed")
                              
    }
    else if (cmd == "getRaceOpponents")
	{
		trace("User Id: " + params.userId);
		
		// create a SQL statement
		var sql = "SELECT USR_ID , USR_USERNAME , USR_POINTS , USR_POSITION , USR_VICTORIES , USR_LOSES FROM USER WHERE USR_ID != ";
		sql += _server.escapeQuotes(params.userId) + ";"
		
		// execute query on DB
		// queryRes is a ResultSet object
		var queryRes = dbase.executeQuery(sql)
		
		// prepare the response object
		
		response._cmd = "getRaceOpponents"
		
		// Here we create an array for storing the database data
		response.opponents = {};
		
		if (queryRes != null)
		{	
			// Cycle through all records in the ResultSet
			for (var i = 0; i < queryRes.size(); i++)
			{
				// Get a record
				var tempRow = queryRes.get(i)
				
				// From the record object we can get each field value
				response.opponents[i] = {};
				response.opponents[i].id = tempRow.getItem("USR_ID")
				response.opponents[i].name = tempRow.getItem("USR_USERNAME")
				response.opponents[i].points = tempRow.getItem("USR_POINTS")
				response.opponents[i].position = tempRow.getItem("USR_POSITION")
				response.opponents[i].victories = tempRow.getItem("USR_VICTORIES")
				response.opponents[i].loses = tempRow.getItem("USR_LOSES")
			}
		}
		else
			trace("DB Query failed")
	}
    else if (cmd == "race")
	{
		trace("params.playerId: " + params.playerId)
		trace("params.opponentId: " + params.opponentId)

		// get player data
		player = {} 
		player = getPlayer("", params.playerId)
		
		if (player == null)
			trace("ERROR: getPlayer for race");

		// get opponent data
		opponent = {} 
		opponent = getPlayer("", params.opponentId)

		if (opponent == null)
			trace("ERROR: getOpponent for race");

		// prepare the response object		
		response._cmd = "race"
		
		// calculate race result
		playerResult = ((Number)(player.car.engine.acel) + ((Number)(player.car.tire.maxSpeed) + (Number)(player.car.engine.maxSpeed)) / 2 + (Number)(player.car.tire.grip)) * (Math.random()*100);
		opponentResult = ((Number)(opponent.car.engine.acel) + ((Number)(opponent.car.tire.maxSpeed) + (Number)(opponent.car.engine.maxSpeed)) / 2 + (Number)(opponent.car.tire.grip)) * (Math.random()*100);

		trace ("apunto de asignar los valores de quien gano");
		trace ("player money:"+player.money);
		
		if (playerResult >= opponentResult)
		{
			response.result = "win"

			response.money = (Number)(player.money) + 500;
			response.points = (Number)(player.points) + 3;
			response.position = player.position;
			response.victories = (Number)(player.victories) + 1;
			response.loses = player.loses;
			response.racesPerDay = (Number)(player.racesPerDay) - 1;
		}
		else
		{
			response.result = "lose"
			
			response.money = (Number)(player.money) + 50;
			response.points = (Number)(player.points) + 1;
			response.position = player.position;
			response.victories = player.victories;
			response.loses = (Number)(player.loses) + 1;
			response.racesPerDay = (Number)(player.racesPerDay) - 1;
		}
		
		if (!updatePlayer(response, params.playerId))
			trace("ERROR updating player after race")
			
		var sql = "SELECT USR_POSITION FROM USER WHERE USR_ID = ";
		sql += _server.escapeQuotes(params.playerId) + ";"

		// execute query on DB
		// queryRes is a ResultSet object
		var queryRes = dbase.executeQuery(sql)
		
		if (queryRes.size() != 1)
		{
			// GRAN EXCEPTION!!!!
			trace("GRAN EXCEPTION!!!!")
		}
		else
		{
			// Get a record
			var tempRow = queryRes.get(0)
			
			response.position = tempRow.getItem("USR_POSITION");
		}
	/*
		// Create the SQL statement to update the record
		var sql = "UPDATE USER SET "
		sql += " USR_MONEY='" + _server.escapeQuotes(response.money) + "',"
		sql += " USR_POINTS='" + _server.escapeQuotes(response.points) + "',"
		sql += " USR_POSITION='" + _server.escapeQuotes(response.position) + "',"
		sql += " USR_VICTORIES='" + _server.escapeQuotes(response.victories) + "',"
		sql += " USR_LOSES='" + _server.escapeQuotes(response.loses) + "',"
		sql += " USR_RACES_PER_DAY='" + _server.escapeQuotes(response.racesPerDay) + "'"
		sql += " WHERE USR_ID='" + _server.escapeQuotes(params.playerId) + "';"
		
		trace ("query a ejecutar:"+sql);
		// execute query on DB
		// queryRes is a ResultSet object
		var success = dbase.executeCommand(sql)
		
		if (!success)
			trace ("ERROR UDPATING USER")
			
	*/
	}
    else if (cmd == "sendMail")
	{
		// create a SQL statement
		var sql = "SELECT USR_MAIL FROM USER WHERE USR_USERNAME = '";
		sql += _server.escapeQuotes(params.username) + "';"	

		if (queryRes != null)
		{
			if (queryRes.size() == 0)
			{
				//create new player here
				trace("create new player here")
			}
			else if (queryRes.size() != 1)
			{
				// GRAN EXCEPTION!!!!
				trace("GRAN EXCEPTION!!!!")
			}
			else
			{
				// Get a record
				var tempRow = queryRes.get(0)
				
				// This object will hold the record data that we'll send to the client
				var mail = tempRow.getItem("USR_MAIL");
				
				trace("SMTP SERVER NOT SET!!!");
				//_server.sendMail(from, to, subject, message)		
			}
		}
	}
_server.sendResponse(response, -1, null, [user])
}


function handleInternalEvent(evt)
{
	// Simply print the name of the event that was received
	trace("Event received: " + evt.name)
}

function updateUserPosition(userId) 
{
	sql = "SELECT USR_POSITION FROM USER WHERE USR_ID = "
	sql += userId 
	sql += ";"
	
	var queryRes = dbase.executeQuery(sql)
	
	if (queryRes.size() != 1)
	{
		trace("ERROR updateUserPosition GETTING ACTUAL POS")
	}
	
	var tempRow = queryRes.get(0)
	
	position = tempRow.getItem("USR_POSITION");

	sql = "UPDATE USER SET USR_POSITION = (SELECT COUNT (USR_POINTS) FROM USER WHERE USR_POINTS >= (SELECT USR_POINTS FROM USER WHERE USR_ID = "
	sql += userId 
	sql += ")) WHERE USR_ID = "
	sql += userId 
	sql += "; UPDATE USER SET USR_POSITION = USR_POSITION + 1 WHERE USR_POSITION < "
	sql += position
	sql += " AND USR_POSITION >= (SELECT USR_POSITION FROM USER WHERE USR_ID = "
	sql += userId 
	sql += ") AND USR_ID != "
	sql += userId 
	sql += ";"

	trace("Query en updateUserMoneyAndRaces: " + sql)
	
	success = dbase.executeCommand(sql)
	
	if (!success)
	{
		trace("ERROR updateUserMoneyAndRaces UPDATING POS")
	}	
}

function createPlayer(userName, userId)
{
	//dbase.executeCommand("SET AUTOCOMMIT OFF")
	
	//create new player here
	sql = "INSERT INTO CARS ( CAR_TIRES , CAR_ENGINE , CAR_COLOR , CAR_ID ) VALUES (0, 0, 0, " 
	sql += _server.escapeQuotes(userId) + ");"

	// execute query on DB
	// queryRes is a ResultSet object
	var successCar = dbase.executeCommand(sql)
	
	sql = "INSERT INTO USER (USR_POINTS , USR_POSITION , USR_VICTORIES , USR_LOSES , USR_RACES_PER_DAY , USR_MONEY , USR_LAST_LOGIN , USR_ID , USR_CAR , USR_USERNAME) VALUES (0, 0, 0, 0, 5, 5000, now(), "
	sql += _server.escapeQuotes(userId) + ", "
	sql += _server.escapeQuotes(userId) + ", "
	sql += "'" + _server.escapeQuotes(userName) + "');"

	
	// execute query on DB
	// queryRes is a ResultSet object
	var successUser = dbase.executeCommand(sql)
	
	trace("successCar: " + successCar);
	trace("successUser: " + successUser);
	if(successCar && successUser)
	{
		//dbase.executeCommand("COMMIT");
		trace("return true");
		return true;
	}
	else
	{
		//dbase.executeCommand("ROLLBACK");
		trace("return false");
		return false;
	}

	//dbase.executeCommand("SET AUTOCOMMIT ON")
}

function getPlayer(userName, userId)
{
	// create a SQL statement
	var sql = "SELECT * FROM USER AS U, CARS AS C, ENGINES AS E, TIRES AS T "
	sql += "WHERE U.USR_CAR = C.CAR_ID AND C.CAR_TIRES = T.TIR_ID AND C.CAR_ENGINE = E.ENG_ID AND U.USR_ID = ";
	sql += _server.escapeQuotes(userId) + ";"

	// execute query on DB
	// queryRes is a ResultSet object
	var queryRes = dbase.executeQuery(sql)
	
	if (queryRes.size() == 0)
	{
		if (createPlayer(userName, userId))
		{
			trace("getPlayer");
			return getPlayer(userName, userId)
		}
		else
		{
			trace("null");
			return null
		}
	}
	else if (queryRes.size() != 1)
	{
		// GRAN EXCEPTION!!!!
		trace("GRAN EXCEPTION!!!!")
	}
	else
	{
		var player;
		player = {}

		// Get a record
		var tempRow = queryRes.get(0)
		
		// This object will hold the record data that we'll send to the client
		player.id = tempRow.getItem("USR_ID");
		player.username = tempRow.getItem("USR_USERNAME");
		player.money = tempRow.getItem("USR_MONEY");
		player.points = tempRow.getItem("USR_POINTS");
		player.position = tempRow.getItem("USR_POSITION");
		player.victories = tempRow.getItem("USR_VICTORIES");
		player.loses = tempRow.getItem("USR_LOSES");
		player.racesPerDay = tempRow.getItem("USR_RACES_PER_DAY");
		
		player.car = {};
		player.car.tire = {};
		player.car.engine = {};
		
		player.car.tire.id	= tempRow.getItem("TIR_ID")
		player.car.tire.name = tempRow.getItem("TIR_NAME")
		player.car.tire.grip = tempRow.getItem("TIR_GRIP")
		player.car.tire.maxSpeed = tempRow.getItem("TIR_MAX_SPEED")
		player.car.tire.price = tempRow.getItem("TIR_PRICE")
		
		player.car.engine.id = tempRow.getItem("ENG_ID")
		player.car.engine.name	= tempRow.getItem("ENG_NAME")
		player.car.engine.acel = tempRow.getItem("ENG_ACELERATION")
		player.car.engine.maxSpeed = tempRow.getItem("ENG_MAX_SPEED")
		player.car.engine.price = tempRow.getItem("ENG_PRICE")
		
		player = updateUserMoneyAndRaces(userId, player)
		
		return player;
	}
}

function updatePlayer(player, playerId)
{
	// Create the SQL statement to update the record
	var sql = "UPDATE USER SET "
	sql += " USR_MONEY='" + _server.escapeQuotes(player.money) + "',"
	sql += " USR_POINTS='" + _server.escapeQuotes(player.points) + "',"
	sql += " USR_VICTORIES='" + _server.escapeQuotes(player.victories) + "',"
	sql += " USR_LOSES='" + _server.escapeQuotes(player.loses) + "',"
	sql += " USR_RACES_PER_DAY='" + _server.escapeQuotes(player.racesPerDay) + "'"
	sql += " WHERE USR_ID='" + _server.escapeQuotes(playerId) + "';"
	
	trace ("query a ejecutar:"+sql);
	// execute query on DB
	// queryRes is a ResultSet object
	var success = dbase.executeCommand(sql)
	
	if (success)
	{
		updateUserPosition(playerId)
		return true
	}
	else
	{
		trace ("ERROR UDPATING USER")
		return false
	}
	
	updateUserPosition(playerId)
}

function updateUserMoneyAndRaces(userId, player)
{
	sql = "SELECT DATEDIFF('DAY', (SELECT USR_LAST_LOGIN FROM USER WHERE USR_ID="
	sql += userId 
	sql += "), now()) AS DIFF"
	
	var queryRes = dbase.executeQuery(sql)
	
	if (queryRes.size() != 1)
	{
		trace("ERROR updateUserMoneyAndRaces")
	}
	
	var tempRow = queryRes.get(0)
	
	trace("item DIIF = " + tempRow.getItem("DIFF"))
	
	if((tempRow.getItem("DIFF") - 1) > 0)
	{
		player.racesPerDay = 5
		player.money = (Number)(player.money) + (Number)((tempRow.getItem("DIFF") - 1)) * 1000
	}
	
	var sql = "UPDATE USER SET "
	sql += " USR_MONEY='" + _server.escapeQuotes(player.money) + "',"
	sql += " USR_RACES_PER_DAY='" + _server.escapeQuotes(player.racesPerDay) + "',"
	sql += " USR_LAST_LOGIN=now()"
	sql += " WHERE USR_ID='" + _server.escapeQuotes(userId) + "';"
	
	// execute query on DB
	// queryRes is a ResultSet object
	var success = dbase.executeCommand(sql)
	
	if (!success)
	{
		trace ("ERROR UDPATING USER IN updateUserMoneyAndRaces")
	}
	
	return player
}